
package com.york.controller;


import cn.hutool.core.date.DateUtil;
import com.york.entity.User;
import com.york.service.UserService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


/**
 * @author YORK
 */
@Controller
public class PoiController {
    @Autowired
    private UserService userService;


    /**
     * @description 根据原生方式导出excel文件，文件类型是xls的
     * http://localhost:8081/exportBy03
     */
    @GetMapping("exportBy03")
    public void exportUserBy03(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream()) {
            List<User> users = userService.selectList();
            Workbook workbook = new HSSFWorkbook();
            String fileName = "用户列表03.xls";
            Sheet sheet = workbook.createSheet("用户列表");
            Row rowTitle = sheet.createRow(0);
            rowTitle.createCell(0).setCellValue("用户名");
            rowTitle.createCell(1).setCellValue("邮箱");
            rowTitle.createCell(2).setCellValue("时间");
            rowTitle.createCell(3).setCellValue("城市");
            rowTitle.createCell(4).setCellValue("状态");
            for (int i = 1; i < users.size(); i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(users.get(i).getUsername());
                row.createCell(1).setCellValue(users.get(i).getEmail());
                row.createCell(2).setCellValue(DateUtil.format(users.get(i).getCreated(), "yyyy-MM-dd"));
                row.createCell(3).setCellValue(users.get(i).getCity());
                row.createCell(4).setCellValue(users.get(i).getStatu());
            }
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * @description 根据原生方式导入excel文件，文件类型是xls的
     * http://localhost:8081/importBy03
     */
    @GetMapping("importBy03")
    public void importUserBy03(MultipartFile file) {
        try (InputStream is = file.getInputStream()) {
            List<User> users = new ArrayList<>();
            Workbook workbook = new HSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();

            for (int i = 1; i < rows; i++) {
                Row row = sheet.getRow(i);
                User user = new User();
                user.setUsername(getValueByCell(row.getCell(0)).toString());
                user.setEmail(getValueByCell(row.getCell(1)).toString());
                user.setCreated((Date) getValueByCell(row.getCell(2)));
                user.setCity(getValueByCell(row.getCell(3)).toString());
                user.setStatu(Integer.valueOf(getValueByCell(row.getCell(4)).toString()));
                users.add(user);
            }
            System.out.println(users);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private Object getValueByCell(Cell cell) {
        Object value = null;
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getStringCellValue();
                }
                break;
            case FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }

    /**
     * @description 根据原生方式导入excel文件，文件类型是xlsx的
     * http://localhost:8081/exportBy07
     */
    @GetMapping("exportBy07")
    public void exportUserBy07(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream()) {
            List<User> users = userService.selectList();
            Workbook workbook = new XSSFWorkbook();
            String fileName = "用户列表07.xlsx";
            Sheet sheet = workbook.createSheet("用户列表");
            Row rowTitle = sheet.createRow(0);
            rowTitle.createCell(0).setCellValue("用户名");
            rowTitle.createCell(1).setCellValue("邮箱");
            rowTitle.createCell(2).setCellValue("时间");
            rowTitle.createCell(3).setCellValue("城市");
            rowTitle.createCell(4).setCellValue("状态");
            for (int i = 1; i < users.size(); i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(users.get(i).getUsername());
                row.createCell(1).setCellValue(users.get(i).getEmail());
                row.createCell(2).setCellValue(DateUtil.format(users.get(i).getCreated(), "yyyy-MM-dd"));
                row.createCell(3).setCellValue(users.get(i).getCity());
                row.createCell(4).setCellValue(users.get(i).getStatu());
            }
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }
    /**
     * @description 根据原生方式导入excel文件，文件类型是xlsx的
     * http://localhost:8081/exportBy07Big
     */
    @GetMapping("exportBy07Big")
    public void exportUserBy07Big(HttpServletResponse response) {
        try (OutputStream os = response.getOutputStream()) {
            List<User> users = userService.selectList();
            Workbook workbook = new SXSSFWorkbook();
            String fileName = "用户列表07Big.xlsx";
            Sheet sheet = workbook.createSheet("用户列表");
            Row rowTitle = sheet.createRow(0);
            rowTitle.createCell(0).setCellValue("用户名");
            rowTitle.createCell(1).setCellValue("邮箱");
            rowTitle.createCell(2).setCellValue("时间");
            rowTitle.createCell(3).setCellValue("城市");
            rowTitle.createCell(4).setCellValue("状态");
            for (int i = 1; i < users.size(); i++) {
                Row row = sheet.createRow(i);
                row.createCell(0).setCellValue(users.get(i).getUsername());
                row.createCell(1).setCellValue(users.get(i).getEmail());
                row.createCell(2).setCellValue(DateUtil.format(users.get(i).getCreated(), "yyyy-MM-dd"));
                row.createCell(3).setCellValue(users.get(i).getCity());
                row.createCell(4).setCellValue(users.get(i).getStatu());
            }
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}





